{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 交易频率分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "from pyecharts.commons.utils import JsCode\n",
    "import sys\n",
    "sys.path.append('..')\n",
    "from settings import DBSelector\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DB=DBSelector()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = DB.get_engine('db_stock','qq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table = 'tb_delivery'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql(table,con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index('成交日期',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# GJ\n",
    "df=df[(df['操作']=='证券卖出')| (df['操作']=='证券卖出')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df[(df['委托类别']=='卖出')| (df['委托类别']=='买入')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "result = df.resample('W').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "result=result.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result=result.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np_dt =np.datetime64('2018-09-01T00:00:00.000000')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(np_dt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dir(np_dt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convertor(x):\n",
    "    print(x.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result['成交日期']=result.map(lambda x:x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result=result['证券代码']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "xticklabels=list(result.index)[::5]\n",
    "xticks=range(0,len(xticklabels),5)\n",
    "result.plot.bar(xticks=xticks,xticklabels=xticklabels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig,ax = plt.subplots()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(figsize=(10,8))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = plt.gca()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=list(result.index)\n",
    "y=list(result.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "plt.bar(x,y,width=0.6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ax.set_xticks(xticks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ax.set_xticklabels(xticklabels,rotation=45)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.plot(x,y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "fig,ax=plt.subplots()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ax.set_xticks(xticks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ax.set_xticklabels(xticklabels,rotation=45)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.bar(x,y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.grid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "xticks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "xticklabels=list(result.index)[::5]\n",
    "xticks=range(0,len(result),5)\n",
    "# result.plot.bar(xticks=xticks,xticklabels=xticklabels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "xticklabels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(figsize=(20,10))\n",
    "result.plot.bar(width=0.8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts import Bar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=result.index.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x_py_dt=[i.to_pydatetime() for i in x]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=result['成交日期'].dt.to_pydatetime()\n",
    "x_py_dt=[i.strftime('%Y-%m-%d') for i in x]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x_py_dt=[i.strftime('%Y-%m-%d') for i in x]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "x_py_dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=x_py_dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(y_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "y=result['成交数量'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Bar.add"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bar = Bar('交易频率柱状图')\n",
    "bar.add('交易频率',x_py_dt,y,mark_line=['average'])\n",
    "bar.render()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Bar\n",
    "\n",
    "bar=(\n",
    "    Bar()\n",
    "    .add_xaxis(x_py_dt)\n",
    "    .add_yaxis('A',y)\n",
    "    .set_global_opts(title_opts=opts.TitleOpts(title=\"Bar-MarkPoint（指定类型）\")).set_series_opts(\n",
    "            label_opts=opts.LabelOpts(is_show=True),\n",
    "            markpoint_opts=opts.MarkPointOpts(\n",
    "                data=[\n",
    "                    opts.MarkPointItem(type_=\"max\", name=\"最大值\"),\n",
    "                    opts.MarkPointItem(type_=\"min\", name=\"最小值\"),\n",
    "                    opts.MarkPointItem(type_=\"average\", name=\"平均值\"),\n",
    "                ]\n",
    "            ),\n",
    "        )\n",
    "    )\n",
    "\n",
    "bar.render()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from example import Faker\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Bar\n",
    "x=[1,2,3,4,5,6,7,8,9]\n",
    "y=[1,2,3,4,5,6,7,8,9]\n",
    "bar=(\n",
    "    Bar()\n",
    "        .add_xaxis(Faker.choose())\n",
    "        .add_yaxis(\"商家A\", x)\n",
    "        .add_yaxis(\"商家B\", y))\n",
    "        .set_global_opts(title_opts=opts.TitleOpts(title=\"Bar-MarkPoint（指定类型）\"))\n",
    "        .set_series_opts(\n",
    "            label_opts=opts.LabelOpts(is_show=False),\n",
    "            markpoint_opts=opts.MarkPointOpts(\n",
    "                data=[\n",
    "                    opts.MarkPointItem(type_=\"max\", name=\"最大值\"),\n",
    "                    opts.MarkPointItem(type_=\"min\", name=\"最小值\"),\n",
    "                    opts.MarkPointItem(type_=\"average\", name=\"平均值\"),\n",
    "                ]\n",
    "            ),\n",
    "        )\n",
    "    )\n",
    "\n",
    "bar.render()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=result['成交日期'].dt.to_pydatetime()\n",
    "x_py_dt=[i.strftime('%Y-%m-%d') for i in x]\n",
    "y=result['成交数量'].values\n",
    "y = list(map(lambda x:int(x),y))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(x_py_dt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Bar\n",
    "\n",
    "bar=(\n",
    "    Bar()\n",
    "        .add_xaxis(x_py_dt)\n",
    "        .add_yaxis(\"交易频率\",y,itemstyle_opts=opts.ItemStyleOpts(\n",
    "            color=JsCode(color_function)),)\n",
    "        .set_global_opts(title_opts=opts.TitleOpts(title=\"交易频率 券商B\"),\n",
    "                         xaxis_opts=opts.AxisOpts(axislabel_opts={\"rotate\":45}))\n",
    "        .set_series_opts(\n",
    "            label_opts=opts.LabelOpts(is_show=True),\n",
    "            datazoom_opts=opts.DataZoomOpts(),\n",
    "            markline_opts=opts.MarkLineOpts(\n",
    "            data=[\n",
    "                opts.MarkLineItem(type_=\"average\", name=\"平均值\"),\n",
    "            ]\n",
    "            ),\n",
    "\n",
    "        ).render('B.html')\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "color_function = \"\"\"\n",
    "        function (params) {\n",
    "            if (params.value > 0 && params.value <= 30) {\n",
    "                return '#1a7f0f';\n",
    "            } else if (params.value > 30 && params.value <=60) {\n",
    "                return '#e8e855';\n",
    "            } else if (params.value > 60)\n",
    "            { return '#C1232B';}\n",
    "            return 'green';\n",
    "        }\n",
    "        \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    " .set_global_opts(title_opts=opts.TitleOpts(title=title, subtitle=subtitle),xaxis_opts=opts.AxisOpts(name_rotate=60,name=\"123456\",axislabel_opts={\"rotate\":45}))\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
